﻿using BCCommon;
using BCEntity.OA.GeneralDatabaseOperation;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData.OA.GeneralDatabaseOperation
{
    public class GeneralDatabasesOperationData : IGeneralDatabasesOperationData
    {
        private const string QUERY_DATABASE_NAME = @"SELECT SCHEMA_NAME AS DatabaseName,SCHEMA_NAME AS DatabaseComment FROM INFORMATION_SCHEMA.SCHEMATA";
        private const string QUERY_TABLE_NAME = @"SELECT table_name AS TableName,table_comment AS TableComment FROM information_schema.tables";
        private const string QUERY_COLUMN_NAME = @"SELECT COLUMN_NAME AS ColumnName,IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS Size,
	                                                    COLUMN_TYPE AS ColumnType, COLUMN_DEFAULT AS DefaultValue,ORDINAL_POSITION AS TIndex, COLUMN_COMMENT AS ColumnComment 
                                                    FROM information_schema.COLUMNS";

        private readonly IDatabaseContext databaseContext;

        public GeneralDatabasesOperationData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<IEnumerable<GeneralDatabaseOperationDatabaseNameEntity>> GetAllDatabaseName()
        {
            string sql = string.Format("{0} WHERE SCHEMA_NAME NOT IN ('information_schema','cr_debug','fxs','performance_schema','sys','log','mysql','warning');", QUERY_DATABASE_NAME);
            return await this.databaseContext.Slave.QueryAsync<GeneralDatabaseOperationDatabaseNameEntity>(sql);
        }

        public async Task<IEnumerable<GeneralDatabaseOperationTableNameEntity>> GetAllTableName(string databaseName)
        {
            string sql = string.Format("{0} WHERE table_schema=?DatabaseName", QUERY_TABLE_NAME);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DatabaseName", databaseName, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<GeneralDatabaseOperationTableNameEntity>(sql, parameters);
        }

        public async Task<IEnumerable<GeneralDatabaseOperationColumnNameEntity>> GetAllColumnName(string databaseName, string tableName)
        {
            string sql = string.Format("{0} {1}", QUERY_COLUMN_NAME, "WHERE TABLE_SCHEMA=?DatabaseName AND TABLE_NAME=?TableName");
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?DatabaseName", databaseName, DbType.AnsiString);
            parameters.Add("?TableName", tableName, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<GeneralDatabaseOperationColumnNameEntity>(sql, parameters); 
        }

        public async Task<IEnumerable<Object>> GetTableDataList(string databaseName, string tableName)
        {
            string sql = string.Format("SELECT * FROM {0}", string.Format("{0}.{1}", databaseName, tableName));
            return await this.databaseContext.Slave.QueryAsync<object>(sql);
        }

        public async Task<IEnumerable<object>> GetTableData(string databaseName, string tableName, List<string> columnNameList, List<string> wheres, List<string> symbol, List<Object> parameters, int pageIndex, int pageSize)
        {
            if (wheres != null && wheres.Count > 0 && parameters != null && parameters.Count > 0 && symbol != null && symbol.Count > 0)
            {
                IEnumerable<string> wheresName = wheres.Select(p => p + "=?" + p);
                List<string> whereList = new List<string>();
                for (int i = 0; i < wheresName.Count(); i++)
                {
                    string where = wheresName.ElementAt(i);
                    whereList.Add(where.Replace("=", symbol.ElementAt(i)));
                }
                DynamicParameters dynamicParameters = new DynamicParameters();
                for (int i = 0; i < parameters.Count; i++)
                {
                    dynamicParameters.Add("?" + wheres[i], parameters[i]);
                }

                string sql = string.Format("SELECT {0} FROM {1} {2} LIMIT {3},{4}", string.Join(",", columnNameList),
                    string.Format("{0}.{1}", databaseName,tableName),
                    whereList.Count > 0 ? "WHERE " + string.Join(" AND ", whereList) : "",
                    (pageIndex - 1) * pageSize, pageSize);

                return await this.databaseContext.Slave.QueryAsync<object>(sql, dynamicParameters);
            }
            else
            {
                string sql = string.Format("SELECT {0} FROM {1} LIMIT {2},{3}", string.Join(",", columnNameList),
                    string.Format("{0}.{1}", databaseName, tableName),
                    (pageIndex - 1) * pageSize, pageSize);
                return await this.databaseContext.Slave.QueryAsync<object>(sql);
            }
        }

    }
}
